const Database=require("better-sqlite3");
const path=require("path");
const db=new Database(path.join(__dirname,"./data.sqlite"),{
    fileMustExist:true,
    verbose:console.log
});

const findAllUsers=function(){
    let sql='select id,username,password from t_user';
    let stmt=db.prepare(sql);
    let rows=stmt.all();
    return rows;
};

const findOneUser=function(id=""){
    let sql='select id,username,password from t_user where id=?';
    let stmt=db.prepare(sql);
    let rows=stmt.get(id);
    return rows;
};
const insertClass=function(classObj={}){
    let sql='insert into t_class (name) values (?)';
    let stmt=db.prepare(sql);
    let rows=stmt.run(classObj.name);
    return rows;
};

const findAllStu=function(){
    let sql=
    `select s.id,s.name,s.link,s.img,c.name  cname 
    from t_student s,t_class c 
    where s.cid=c.id`;
    let stmt=db.prepare(sql);
    let rows=stmt.all();
    return rows;
};
const findAllClass=function(id=""){
    let sql='select id,name from t_class';
    let stmt=db.prepare(sql);
    let rows=stmt.all();
    return rows;
};
const insertStu=function(stu={}){
    let sql='insert into t_student (name,link,img,cid) values (?,?,?,?)';
    let stmt=db.prepare(sql);
    let rows=stmt.run(stu.name,stu.link,stu.img,stu.cid);
    return rows;
};

const findStuById=function(id=''){
    let sql=
    `select s.id,s.name,s.link,s.img,c.name  cname 
    from t_student s,t_class c 
    where s.cid=c.id and s.id=?`;
    let stmt=db.prepare(sql);
    let rows=stmt.get(id);
    return rows;
};
module.exports={
    findAllUsers,
    findOneUser,
    insertClass,
    findAllStu,
    findAllClass,
    insertStu,
    findStuById
};